import pandas as pd
import time
import re
import openpyxl
import mysql.connector
from mysql.connector import Error

# 连接数据库,保留连接对象
def create_database_connection(host_name, user_name, user_password, database_name):
    """
    创建并返回一个数据库连接对象。

    参数:
    host_name (str): 数据库主机地址。
    user_name (str): 数据库用户名。
    user_password (str): 数据库密码。
    database_name (str): 数据库名称。

    返回:
    mysql.connector.connection: MySQL 连接对象。
    status:连接情况。
    """
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=database_name
        )
        if connection.is_connected():
            status = "Database connection successful."
    except Error as e:
        status = f"Error while connecting to MySQL: {e}"
    return connection, status
# 执行sql查询指令
def sql_select(connection,sql_query):
    '''
    connection：数据库连接对象
    sql_query：需要执行的sql指令
    '''
    try:
        # 创建游标对象
        cursor = connection.cursor()
        # 执行 SQL 查询语句
        cursor.execute(sql_query)
        # 获取所有查询结果
        results = cursor.fetchall()
        results = [list(row) for row in results] # 将每个元组转换为列表
        return results
    except mysql.connector.Error as error:
        return f"Error: {error}"
    finally:
        # 关闭游标对象
        if cursor is not None:
            cursor.close()
# 查询数据库列名
def sql_column_select(connection,table_name):
    try:
        # 创建游标对象
        cursor = connection.cursor()
        # 编写 SQL 查询语句——查询列名
        describe_query = f"DESCRIBE {table_name}"
        # 执行 SQL 查询语句
        cursor.execute(describe_query)
        # 获取查询结果
        columns = cursor.fetchall()
        columns = [list(row) for row in columns] # 将每个元组转换为列表
        # 提取列名
        column_names = [column[0] for column in columns]  # 假设第一列是列名
        return column_names
    except mysql.connector.Error as error:
        # 如果发生错误则回滚
        connection.rollback()
        print(f"Error: {error}")
    finally:
        # 关闭游标对象
        if cursor is not None:
            cursor.close()
# 执行sql插入指令
def sql_insert(connection,table_name:str,data:list):
    '''
    connection：数据库连接对象
    sql_query：需要执行的sql指令
    '''
    try:
        # 查询列名
        column_names = sql_column_select(connection,table_name)
        # 创建游标对象
        cursor = connection.cursor()
        column_length = len(column_names)
        # 将列名转化为元组形式的字符串
        # 使用 join() 方法将列表转换成字符串
        column_names = ', '.join(column_names)
        column_names = f"({column_names})"
        # 构建占位符%s
        str_s = ', '.join(['%s'] * column_length)
        str_s = f"({str_s})"
        # 编写 SQL 插入语句
        sql_insert_query = f"insert into {table_name} {column_names} values {str_s}"
        # 待插入的数据元组
        data = [tuple(row) for row in data]
        # 执行 SQL 插入语句
        cursor.executemany(sql_insert_query, data)
        # 提交事务
        connection.commit()
        # 打印受影响的行数
        print(cursor.rowcount, "条数据已插入！")

    except mysql.connector.Error as error:
        # 如果发生错误则回滚
        connection.rollback()
        print(f"Error: {error}")

    finally:
        # 关闭游标对象
        if cursor is not None:
            cursor.close()
# 关闭数据库连接
def close_database_connection(connection):
    if connection and connection.is_connected():
        connection.close()
        print("Database connection closed.")